<?php
namespace app\index\controller;
use app\api\model\Single as SingleModel;
use think\Db;
use think\Controller;
class Excel extends Controller
{
    public function dx()
    {
        return $this->fetch();        
    }
    public function dzp()
    {
        return $this->fetch();        
    }
    public function xp()
    {
        return $this->fetch();        
    }
    public function fy()
    {
        return $this->fetch();        
    }
    //单项
    public function dx_save(){
        $file = $_FILES['file'];
        if ($file['error'] == 4) $this->error('请选择上传excel文件');
        $file_types = explode ( ".", $file['name'] );
        $excel_type = array('xls','csv','xlsx');
        if (!in_array(strtolower(end($file_types)),$excel_type)){
        $this->error("不是Excel文件，请重新上传");
        }

        include(EXTEND_PATH.'phpexcel/PHPExcel/Reader/CSV.php'); 
         
        //设置获取excel对象
        $objReader = new \PHPExcel_Reader_CSV();//配置成2003版本，因为office版本可以向下兼容
        $objReader->setInputEncoding('GBK');
        $objPHPExcel = $objReader->load($file['tmp_name']);//$file 为解读的excel文件
        //dump($objPHPExcel);die;
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $success_item = $fail_item = 0;
        //发货
        $SingleModel = new SingleModel();      
       
        //开始读取数据
        for($j=2;$j<=$highestRow;$j++)
        {
           
                $params=[];
                $params['start_date'] = $objPHPExcel->getActiveSheet()->getCell('A'.$j)->getValue();
                $params['sn'] = $objPHPExcel->getActiveSheet()->getCell('B'.$j)->getValue();
                $category= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue())->find();  
                $params['category_id'] = $category['id']?$category['id']:0;
                $branch= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('D'.$j)->getValue())->find();  
                $params['branch_id'] = $branch['id']?$branch['id']:0;
            
                $params['product_name'] = $objPHPExcel->getActiveSheet()->getCell('H'.$j)->getValue();
                $params['num'] = $objPHPExcel->getActiveSheet()->getCell('I'.$j)->getValue();
                $params['pay_price'] = $objPHPExcel->getActiveSheet()->getCell('J'.$j)->getValue();
                $params['sell_price'] = $objPHPExcel->getActiveSheet()->getCell('K'.$j)->getValue();
                $params['cost_price'] = $objPHPExcel->getActiveSheet()->getCell('O'.$j)->getValue();

                $salesman= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('T'.$j)->getValue())->find();  
                $params['salesman_id'] = $salesman['id']?$salesman['id']:0;

                $operator= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('U'.$j)->getValue())->find();  
                $params['operator_id'] = $operator['id']?$operator['id']:0;

                
                if($objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue()!=null){
                    Db::name('guest')->insert(['guest_name'=>$objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue(),'operator_id'=>$operator['id']]);  
                    $guesId = Db::name('guest')->getLastInsID();
                    $params['guest_id'] = $guesId;
                }
                
                $params['client_id'] = $this->client($objPHPExcel->getActiveSheet()->getCell('E'.$j)->getValue(),$operator['id']);
                $params['supplier_id'] = $this->supplier($objPHPExcel->getActiveSheet()->getCell('G'.$j)->getValue(),$operator['id']);

                $params['type'] =4;
                $data = $SingleModel->allowField(true)->isUpdate(false)->data($params)->save();
                if($data){
                    $success_item++;
                }else{
                    $fail_item++;
                }
           
        }

        $this->success('成功条数:'.$success_item.'，失败条数：'.$fail_item);
    }
    //电子票
    public function dzp_save(){
        $file = $_FILES['file'];
        if ($file['error'] == 5) $this->error('请选择上传excel文件');
        $file_types = explode ( ".", $file['name'] );
        $excel_type = array('xls','csv','xlsx');
        if (!in_array(strtolower(end($file_types)),$excel_type)){
        $this->error("不是Excel文件，请重新上传");
        }

        include(EXTEND_PATH.'phpexcel/PHPExcel/Reader/CSV.php'); 
         
        //设置获取excel对象
        $objReader = new \PHPExcel_Reader_CSV();//配置成2003版本，因为office版本可以向下兼容
        $objReader->setInputEncoding('GBK');
        $objPHPExcel = $objReader->load($file['tmp_name']);//$file 为解读的excel文件
        //dump($objPHPExcel);die;
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $success_item = $fail_item = 0;
        //发货
        $SingleModel = new SingleModel();      
       
        //开始读取数据
        for($j=2;$j<=$highestRow;$j++)
        {
           
                $params=[];
                $params['start_date'] = $objPHPExcel->getActiveSheet()->getCell('A'.$j)->getValue();
                $params['sn'] = $objPHPExcel->getActiveSheet()->getCell('B'.$j)->getValue();
                $category= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue())->find();  
                $params['category_id'] = $category['id']?$category['id']:0;
                $branch= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('D'.$j)->getValue())->find();  
                $params['branch_id'] = $branch['id']?$branch['id']:0;
            
                $params['product_name'] = $objPHPExcel->getActiveSheet()->getCell('H'.$j)->getValue();
                $params['num'] = $objPHPExcel->getActiveSheet()->getCell('I'.$j)->getValue();
                $params['pay_price'] = $objPHPExcel->getActiveSheet()->getCell('J'.$j)->getValue();
                $params['sell_price'] = $objPHPExcel->getActiveSheet()->getCell('K'.$j)->getValue();
                $params['cost_price'] = $objPHPExcel->getActiveSheet()->getCell('O'.$j)->getValue();

                $salesman= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('T'.$j)->getValue())->find();  
                $params['salesman_id'] = $salesman['id']?$salesman['id']:0;

                $operator= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('U'.$j)->getValue())->find();  
                $params['operator_id'] = $operator['id'];

            
                if($objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue()!=null){
                    Db::name('guest')->insert(['guest_name'=>$objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue(),'operator_id'=>$operator['id']]);  
                    $guesId = Db::name('guest')->getLastInsID();
                    $params['guest_id'] = $guesId;
                }
                $params['client_id'] = $this->client($objPHPExcel->getActiveSheet()->getCell('E'.$j)->getValue(),$operator['id']);
                $params['supplier_id'] = $this->supplier($objPHPExcel->getActiveSheet()->getCell('G'.$j)->getValue(),$operator['id']);

                $params['type'] =5;
                $data = $SingleModel->allowField(true)->isUpdate(false)->data($params)->save();
                if($data){
                    $success_item++;
                }else{
                    $fail_item++;
                }
           
        }

        $this->success('成功条数:'.$success_item.'，失败条数：'.$fail_item);
    }

    //现票
    public function xp_save(){
        $file = $_FILES['file'];
        if ($file['error'] == 8) $this->error('请选择上传excel文件');
        $file_types = explode ( ".", $file['name'] );
        $excel_type = array('xls','csv','xlsx');
        if (!in_array(strtolower(end($file_types)),$excel_type)){
        $this->error("不是Excel文件，请重新上传");
        }

        include(EXTEND_PATH.'phpexcel/PHPExcel/Reader/CSV.php'); 
         
        //设置获取excel对象
        $objReader = new \PHPExcel_Reader_CSV();//配置成2003版本，因为office版本可以向下兼容
        $objReader->setInputEncoding('GBK');
        $objPHPExcel = $objReader->load($file['tmp_name']);//$file 为解读的excel文件
        //dump($objPHPExcel);die;
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $success_item = $fail_item = 0;
        //发货
        $SingleModel = new SingleModel();      
       
        //开始读取数据
        for($j=2;$j<=$highestRow;$j++)
        {
           
                $params=[];
                $params['start_date'] = $objPHPExcel->getActiveSheet()->getCell('A'.$j)->getValue();
                $params['sn'] = $objPHPExcel->getActiveSheet()->getCell('B'.$j)->getValue();
                $category= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue())->find();  
                $params['category_id'] = $category['id']?$category['id']:0;
                $branch= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('D'.$j)->getValue())->find();  
                $params['branch_id'] = $branch['id']?$branch['id']:0;
                $params['ticket_num'] = $objPHPExcel->getActiveSheet()->getCell('H'.$j)->getValue();
                $params['product_name'] = $objPHPExcel->getActiveSheet()->getCell('I'.$j)->getValue();
                $params['num'] = $objPHPExcel->getActiveSheet()->getCell('J'.$j)->getValue();
                $params['pay_price'] = $objPHPExcel->getActiveSheet()->getCell('K'.$j)->getValue();
                $params['sell_price'] = $objPHPExcel->getActiveSheet()->getCell('L'.$j)->getValue();
                $params['cost_price'] = $objPHPExcel->getActiveSheet()->getCell('P'.$j)->getValue();

                $salesman= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('R'.$j)->getValue())->find();  
                $params['salesman_id'] = $salesman['id']?$salesman['id']:0;

                $operator= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('S'.$j)->getValue())->find();  
                $params['operator_id'] = $operator['id']?$operator['id']:0;

                
                if($objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue()!=null){
                    Db::name('guest')->insert(['guest_name'=>$objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue(),'operator_id'=>$operator['id']]);  
                    $guesId = Db::name('guest')->getLastInsID();
                    $params['guest_id'] = $guesId;
                }
                $params['client_id'] = $this->client($objPHPExcel->getActiveSheet()->getCell('E'.$j)->getValue(),$operator['id']);
                $params['supplier_id'] = $this->supplier($objPHPExcel->getActiveSheet()->getCell('G'.$j)->getValue(),$operator['id']);

                $params['type'] =8;
                $data = $SingleModel->allowField(true)->isUpdate(false)->data($params)->save();
                if($data){
                    $success_item++;
                }else{
                    $fail_item++;
                }
           
        }

        $this->success('成功条数:'.$success_item.'，失败条数：'.$fail_item);
    }

    //费用
    public function fy_save(){
        $file = $_FILES['file'];
        if ($file['error'] == 4) $this->error('请选择上传excel文件');
        $file_types = explode ( ".", $file['name'] );
        $excel_type = array('xls','csv','xlsx');
        if (!in_array(strtolower(end($file_types)),$excel_type)){
        $this->error("不是Excel文件，请重新上传");
        }

        include(EXTEND_PATH.'phpexcel/PHPExcel/Reader/CSV.php'); 
         
        //设置获取excel对象
        $objReader = new \PHPExcel_Reader_CSV();//配置成2003版本，因为office版本可以向下兼容
        $objReader->setInputEncoding('GBK');
        $objPHPExcel = $objReader->load($file['tmp_name']);//$file 为解读的excel文件
        //dump($objPHPExcel);die;
        $sheet = $objPHPExcel->getSheet(0);
        $highestRow = $sheet->getHighestRow(); // 取得总行数
        $success_item = $fail_item = 0;
        //发货
        $SingleModel = new SingleModel();      
       
        //开始读取数据
        for($j=2;$j<=$highestRow;$j++)
        {
            
            $params=[];
            $params['start_date'] = $objPHPExcel->getActiveSheet()->getCell('A'.$j)->getValue();
            $params['sn'] = $objPHPExcel->getActiveSheet()->getCell('B'.$j)->getValue();
            $category= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('C'.$j)->getValue())->find();  
            $params['category_id'] = $category['id']?$category['id']:0;
            $branch= Db::name('category')->where("title",$objPHPExcel->getActiveSheet()->getCell('D'.$j)->getValue())->find();  
            $params['branch_id'] = $branch['id']?$branch['id']:0;
           
            $params['product_name'] = $objPHPExcel->getActiveSheet()->getCell('G'.$j)->getValue();
            $params['num'] = $objPHPExcel->getActiveSheet()->getCell('H'.$j)->getValue();
            
            $params['sell_price'] = $objPHPExcel->getActiveSheet()->getCell('I'.$j)->getValue();
            $params['cost_price'] = $objPHPExcel->getActiveSheet()->getCell('L'.$j)->getValue();
            $params['remark'] = $objPHPExcel->getActiveSheet()->getCell('O'.$j)->getValue()?$objPHPExcel->getActiveSheet()->getCell('O'.$j)->getValue():"";

            $operator= Db::name('manager')->where("cn_name",$objPHPExcel->getActiveSheet()->getCell('Q'.$j)->getValue())->find();  
            $params['operator_id'] = $operator['id']?$operator['id']:0;
            if($objPHPExcel->getActiveSheet()->getCell('E'.$j)->getValue()!=null){
                $params['client_id'] = $this->client($objPHPExcel->getActiveSheet()->getCell('E'.$j)->getValue(),$operator['id']);
            }
            
            $params['supplier_id'] = $this->supplier($objPHPExcel->getActiveSheet()->getCell('F'.$j)->getValue(),$operator['id']);

            $params['type'] =9;
            $data = $SingleModel->allowField(true)->isUpdate(false)->data($params)->save();
            if($data){
                $success_item++;
            }else{
                $fail_item++;
            }
          
            
        }
        $this->success('成功条数:'.$success_item.'，失败条数：'.$fail_item);
    }
    public function client($name,$operator_id){
        if($name){
            $client = Db::name('client')->where("company_name",$name)->find();            
            if($client){
            return $client['id'];            
            }else{
            return 0;          
            }
        }
        
      }
  
      public function supplier($name,$operator_id){
        $supplierID = Db::name('supplier')->where("supplier_name",$name)->value('id');            
        if($supplierID){
           return $supplierID;
        }else{           
           return 0;
        }
      }
      public function operator($name){
        $operatorID = Db::name('manager')->where("cn_name",$name)->value('id');            
        if($operatorID){
           return $operatorID;        
        }else{           
           return 0;        
        }
      }
}
